package com.amtf.datafile2.core.excel;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import com.amtf.datafile2.DataException;
import com.amtf.datafile2.DataField;
import com.amtf.datafile2.DataRecord;
import com.amtf.datafile2.DataTable;
import com.amtf.datafile2.cfg.excel.ExcelDataFieldDefinition;
import com.amtf.datafile2.cfg.excel.ExcelDataTableDefinition;
import com.amtf.datafile2.core.std.StdDataTable;

public class ExcelDataTable extends StdDataTable<ExcelDataTableDefinition, ExcelDataFieldDefinition> {
	private Sheet workSheet = null;
	private FormulaEvaluator evaluator = null;

	private int startReadRow = 0;
	private int startReadColumn = 0;
	private int readRow = 1048576;// 65535;
	private int readColumn = 16384;// 256;
	private int startWriteRow = 0;
	private int startWriteColumn = 0;

	public ExcelDataTable(String name, Sheet workSheet, ExcelDataTableDefinition definition) {
		super(name, definition);
		this.workSheet = workSheet;
		this.definition = definition;

		// if (definition.getFieldCount() != 0) {
		this.readColumn = definition.getFieldCount();
		this.evaluator = workSheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
		// }
	}

	/**
	 * @param startRow
	 *            1,2,3,....
	 * @param startColumn
	 *            1,2,3,....
	 * @param maxRow
	 *            1,2,3,....
	 * @param maxColumn
	 */
	public void setReadingRange(int startRow, int startColumn, int maxRow, int maxColumn) {
		if (startRow <= 1) {
			startRow = 0;
		} else {
			startRow -= 1;
		}
		if (startColumn <= 1) {
			startColumn = 0;
		} else {
			startColumn -= 1;
		}
		// if (maxColumn > 16384 || maxColumn <= 0) {
		// maxColumn = 16384 - 1;
		// }
		// if (maxRow > 1048576 || maxRow <= 0) {
		// maxRow = 1048576 - 1;
		// }
		if (maxColumn <= 0) {
			maxColumn = -1;
		}
		if (maxRow <= 0) {
			maxRow = 1;
		}

		this.startReadRow = startRow;
		this.startReadColumn = startColumn;
		this.readRow = maxRow + startReadRow;
		this.readColumn = maxColumn;

		this.currentReadingLine = startReadRow;
	}

	public void setWritenRange(int startRow, int startColumn) {
		if (startRow < 0) {
			startRow = 0;
		}
		if (startColumn < 0) {
			startColumn = 0;
		}

		this.startWriteRow = startRow;
		this.startWriteColumn = startColumn;

		this.currentWritenLine = startWriteRow;
	}

	private int testColumnCount() {
		int i = startReadRow;
		Row row = this.workSheet.getRow(i);
		while (row == null) {
			row = this.workSheet.getRow(++i);
		}

		i = startReadColumn;
		Cell cell = row.getCell(i);
		while (cell != null) {
			cell = row.getCell(++i);
		}

		return i - startReadColumn;
	}

	public DataRecord readFirstLine() throws IOException, DataException {
		this.currentReadingLine = startReadRow;

		if (definition.getFieldCount() == 0) {
			if (readColumn == 0) {
				readColumn = testColumnCount();
			}

			definition.addFields(readColumn);
		}

		if (definition.isFirstRowTitle()) {
			DataRecord title = readNextLine();
			if (title != null) {
				if (definition.isTitleAsColumnName()) {
					List<ExcelDataFieldDefinition> fielddefs = definition.getFields();
					for (int i = 0; i < fielddefs.size(); i++) {
						ExcelDataFieldDefinition fielddef = fielddefs.get(i);
						fielddef.setName(title.getValue(i));
					}
				}
			}
		}

		DataRecord record = readNextLine();

		return record;
	}

	public DataRecord readNextLine() throws IOException, DataException {
		if (this.currentReadingLine >= this.readRow) {
			return null;
		}

		Row row = this.workSheet.getRow(this.currentReadingLine++);
		if (row != null) {
			return ExcelDataRecord.parseRecord(definition, this.startReadColumn, this.readColumn, row, evaluator);
		}

		return null;
	}

	public List<DataRecord> readFirstLines(int readCount) throws IOException, DataException {
		this.currentReadingLine = startReadRow;

		if (definition.getFieldCount() == 0) {
			if (readColumn == 0) {
				readColumn = testColumnCount();
			}

			definition.addFields(readColumn);
		}

		if (definition.isFirstRowTitle()) {
			readFirstLine();
		}

		List<DataRecord> records = readNextLines(readCount);

		return records;
	}

	public List<DataRecord> readNextLines(int readCount) throws IOException, DataException {
		Row row = null;
		List<DataRecord> records = new ArrayList<DataRecord>(readCount);
		for (int i = 0; i < readCount; i++) {
			if (this.currentReadingLine >= this.readRow) {
				break;
			}

			row = this.workSheet.getRow(this.currentReadingLine++);
			if (row != null) {
				records.add(ExcelDataRecord.parseRecord(definition, this.startReadColumn, this.readColumn, row, evaluator));
			} else {
				break;
			}

			if (readColumn <= 0) {
				readColumn = definition.getFieldCount();
			}
		}
		return records;
	}

	public void writeLine(DataRecord line) throws IOException {
		Row row = this.workSheet.getRow(this.currentWritenLine);
		if (row == null) {
			row = this.workSheet.createRow(this.currentWritenLine);
		}

		DataField[] record = line.getFields();
		for (int i = this.startWriteColumn; i < this.startWriteColumn + record.length; i++) {
			DataField dataField = record[i];

			Cell cell = row.getCell(i);
			if (cell == null) {
				cell = row.createCell(i);
			}

			cell.setCellValue(dataField.getValue());
		}

		this.currentWritenLine++;
	}

	public void writeLines(List<DataRecord> lines) throws IOException {
		for (DataRecord line : lines) {
			writeLine(line);
		}
	}

	public void writeAll(DataTable tableSet) throws IOException, DataException {
		List<DataRecord> lines = tableSet.readFirstLines(500);
		while (lines != null && lines.size() > 0) {
			this.writeLines(lines);
			lines = tableSet.readNextLines(500);
		}
	}

	public void writeLine(String[] line) throws IOException, DataException {
		writeLine(ExcelDataRecord.parseRecord(definition, line));
	}

	public void writeLine(Object[] line) throws IOException, DataException {
		writeLine(ExcelDataRecord.parseRecord(definition, line));
	}

	public void autoSizeColumn(int fromColumn, int toColumn) {
		for (int i = fromColumn; i < toColumn; i++) {
			this.workSheet.autoSizeColumn(i);
		}
	}

	public String getName() {
		return workSheet.getSheetName();
	}

	public void setName(String name) {
		int isht = workSheet.getWorkbook().getSheetIndex(workSheet);
		workSheet.getWorkbook().setSheetName(isht, name);
	}

	public void close() {
	}

	@Override
	public void setDefinition(ExcelDataTableDefinition definition) {
		super.setDefinition(definition);
		this.readColumn = definition.getFieldCount();
	}

}
